package com.kermel.andromeda.data.persistence;

import java.util.Arrays;
import java.util.Locale;
import java.util.UUID;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.kermel.andromeda.data.Alarm;
import com.kermel.common.util.StringUtils;

public class AndromedaDatabaseManager implements SQLConstants {
    public static final AndromedaDatabaseManager instance = new AndromedaDatabaseManager();
    
    public static final String DATABASE_NAME = "andromeda_alarms.db";
    public static final int DATABASE_VERSION = 2;
    protected static final String WHERE_CLAUSE_ROW_ID = "%s=%d";
    
    public static final String ALARMS_TABLE = "table_alarms";
    
    public static final String KEY_ROW_ID = "_id";
    public static final String KEY_NAME = "name";
    public static final String KEY_ENABLED = "enabled";
    public static final String KEY_VIBRATE = "vibrate";
    public static final String KEY_TRIGGER_TIME = "trigger_time";
    public static final String KEY_RINGTONE_URI = "ringtone_uri";
    public static final String KEY_UUID = "uuid";
    
    public static final long UNKNOWN_LONG_VALUE = -1;
    public static final long UNKNOWN_ROW_ID = UNKNOWN_LONG_VALUE;
    
    public static final int INTEGER_FALSE = 0;
    public static final int INTEGER_TRUE = 1;
    
    private static final String[] COLUMNS = { KEY_ROW_ID, KEY_NAME, KEY_ENABLED, KEY_RINGTONE_URI, KEY_TRIGGER_TIME, KEY_UUID };
    private SQLiteDatabase database;
    private String orderByTriggerTime;
    private Context context;
    
    private AndromedaDatabaseManager() {
        orderByTriggerTime = String.format("%s DESC", KEY_TRIGGER_TIME);
    }
    
    public void initializeDatabase(Context context) {
        this.context = context;
        
        boolean databaseDoesNotExist = determineIfDatabaseDoesNotExist();
        
        openOrCreateDatabase();
        
        if (databaseDoesNotExist)
            createAlarmsTableIfNecessary();
    }
    
    public void openOrCreateDatabase() {
        database = context.openOrCreateDatabase(DATABASE_NAME, SQLiteDatabase.CREATE_IF_NECESSARY, null);
        database.setLocale(Locale.getDefault());
        database.setLockingEnabled(true);
        database.setVersion(DATABASE_VERSION);
    }
    
    private boolean determineIfDatabaseDoesNotExist() {
        boolean doesNotExist = false;
        
        String[] databaseList = context.databaseList();
        doesNotExist = Arrays.binarySearch(databaseList, DATABASE_NAME) < 0;
        
        return doesNotExist;
    }
    
    private void createAlarmsTableIfNecessary() {
        String sqlCommand = buildTheCreateAlarmsTableSQLCommand();
        try {
            database.execSQL(sqlCommand);
        } catch (SQLException e) {
            System.err.println(String.format("AndromedaDatabaseManager.createAlarmsTableIfNecessary - Error: %s", e.getMessage()));
        }
    }
    
    private String buildTheCreateAlarmsTableSQLCommand() {
        StringBuilder sb = new StringBuilder(SQL_CREATE_TABLE);
        sb.append(StringUtils.SPACE);
        sb.append(ALARMS_TABLE);
        sb.append(SQL_OPEN_PAREN);
        sb.append(String.format("%s %s", KEY_ROW_ID, SQL_PRIMARY_KEY));
        sb.append(String.format("%s %s, ", KEY_NAME, SQL_TEXT));
        sb.append(String.format("%s %s, ", KEY_ENABLED, SQL_INTEGER));
        sb.append(String.format("%s %s, ", KEY_VIBRATE, SQL_INTEGER));
        sb.append(String.format("%s %s, ", KEY_RINGTONE_URI, SQL_TEXT));
        sb.append(String.format("%s %s, ", KEY_TRIGGER_TIME, SQL_REAL));
        sb.append(String.format("%s %s", KEY_UUID, SQL_TEXT));
        
        sb.append(SQL_TERMINATOR);
        return sb.toString();
    }
    
    public void deleteDatabase() {
        context.deleteDatabase(DATABASE_NAME);
    }
    
    public long addAlarm(Alarm alarm) {
        long result = UNKNOWN_ROW_ID;
        
        if (alarm != null) {
            ContentValues values = createContentValuesFromAlarmWithoutRowID(alarm);
            try {
                result = database.insert(ALARMS_TABLE, null, values);
                alarm.rowID = result;
            } catch (Exception e) {
                System.err.println(String.format("AndromedaDatabaseManager.addAlarm - Error: %s", e.getMessage()));
            }
        }
        
        return result;
    }
    
    protected ContentValues createContentValuesFromAlarm(Alarm alarm) {
        ContentValues values = createContentValuesFromAlarmWithoutRowID(alarm);

        if (alarm != null) {
            values.put(KEY_ROW_ID, alarm.rowID);
        }
        
        return values;
    }
    
    protected ContentValues createContentValuesFromAlarmWithoutRowID(Alarm alarm) {
        ContentValues values = new ContentValues();
        
        if (alarm != null) {
            values.put(KEY_NAME, alarm.name);
            values.put(KEY_ENABLED, alarm.enabled);
            values.put(KEY_RINGTONE_URI, alarm.ringtoneUriString);
            values.put(KEY_TRIGGER_TIME, alarm.triggerTime);
            values.put(KEY_VIBRATE, alarm.vibrate);
            values.put(KEY_UUID, alarm.uuid.toString());
        }
        
        return values;
    }
    
    public Cursor retrieveAllAlarms() {
        Cursor cursor = null;
        
        try {
            cursor = database.query(ALARMS_TABLE, COLUMNS, null, null, null, null, orderByTriggerTime);
        } catch (Exception e) {
            System.err.println(String.format("AndromedaDatabaseManager.retrieveAllAlarms - Error: %s", e.getMessage()));
        }
        
        return cursor;
    }
    
    public Cursor retrieveAlarm(long rowID) {
        Cursor cursor = null;
        String selection = String.format("%s=%d", KEY_ROW_ID, rowID);
        
        try {
            cursor = database.query(true, ALARMS_TABLE, COLUMNS, selection, null, null, null, null, null);
        } catch (Exception e) {
            System.err.println(String.format("AndromedaDatabaseManager.retrieveAlarm - Error: %s", e.getMessage()));
        }
        
        if (cursor != null)
            cursor.moveToFirst();
        
        return cursor;
    }
    
    public int updateAlarm(Alarm alarm) {
        int numberOfRowsAffected = 0;
        
        ContentValues values = createContentValuesFromAlarm(alarm);
        String whereClause = buildWhereClause(alarm.rowID);
        
        try {
            numberOfRowsAffected = database.update(ALARMS_TABLE, values, whereClause, null);
        } catch (Exception e) {
            System.err.println(String.format("AndromedaDatabaseManager.updateAlarm - Error: %s", e.getMessage()));
        }
        
        return numberOfRowsAffected;
    }
    
    private String buildWhereClause(long rowID) {
        String whereClause = String.format(WHERE_CLAUSE_ROW_ID, KEY_ROW_ID, rowID);
        return whereClause;
    }
    
    public boolean deleteAlarm(Alarm alarm) {
        boolean result = false;
        
        String whereClause = buildWhereClause(alarm.rowID);
        try {
            result = database.delete(ALARMS_TABLE, whereClause, null) > 0;
        } catch (Exception e) {
            System.err.println(String.format("AndromedaDatabaseManager.deleteAlarm - Error: %s", e.getMessage()));
        }
        
        return result;
    }
    
    public long getRowID(Cursor cursor) {
        long rowID = UNKNOWN_ROW_ID;
        
        if (cursor != null)
            rowID = retrieveLong(cursor, KEY_ROW_ID);
        
        return rowID;
    }
    
    public String getName(Cursor cursor) {
        String name = StringUtils.EMPTY;
        
        if (cursor != null)
            name = retrieveString(cursor, KEY_NAME);
        
        return name;
    }
    
    public String getRingtoneUri(Cursor cursor) {
        String soundFilename = StringUtils.EMPTY;
        
        if (cursor != null)
            soundFilename = retrieveString(cursor, KEY_RINGTONE_URI);
        
        return soundFilename;
    }
    
    public boolean getEnabled(Cursor cursor) {
        boolean enabled = false;
        
        if (cursor != null)
            enabled = retrieveBoolean(cursor, KEY_ENABLED);
        
        return enabled;
    }
    
    public boolean getVibrate(Cursor cursor) {
        boolean vibrate = false;
        
        if (cursor != null)
            vibrate = retrieveBoolean(cursor, KEY_VIBRATE);
        
        return vibrate;
    }
    
    public long getTriggerTime(Cursor cursor) {
        long triggerTime = UNKNOWN_LONG_VALUE;
        
        if (cursor != null)
            triggerTime = retrieveLong(cursor, KEY_TRIGGER_TIME);
        
        return triggerTime;
    }
    
    public String getUUID(Cursor cursor) {
        String uuidText = StringUtils.EMPTY;
        
        if (cursor != null)
            uuidText = retrieveString(cursor, KEY_UUID);
        
        return uuidText;
    }
    
    public Alarm getAlarm(Cursor cursor) {
        Alarm alarm = new Alarm();
        
        alarm.rowID = getRowID(cursor);
        alarm.name = getName(cursor);
        alarm.ringtoneUriString = getRingtoneUri(cursor);
        alarm.enabled = getEnabled(cursor);
        alarm.vibrate = getVibrate(cursor);
        alarm.triggerTime = getTriggerTime(cursor);
        
        String uuidText = getUUID(cursor);
        
        // TODO: Replace this with if (StringUtils.isNotBlank(uuidText))
        if (uuidText != null && uuidText.length() > 0) 
            alarm.uuid = UUID.fromString(uuidText);
        
        return alarm;
    }
    
    private String retrieveString(Cursor cursor, String columnName) {
        String text = null;
        
        if (cursor != null && columnName != null) {
            int index = cursor.getColumnIndex(columnName);
            if (index >= 0) {
                text = cursor.getString(index);
            }
        }
        return text;
    }
    
    private long retrieveLong(Cursor cursor, String columnName) {
        long value = UNKNOWN_LONG_VALUE;
        
        if (cursor != null && columnName != null) {
            int index = cursor.getColumnIndex(columnName);
            if (index >= 0) {
                value = cursor.getLong(index);
            }
        }
        
        return value;
    }
    
    private boolean retrieveBoolean(Cursor cursor, String columnName) {
        boolean value = false;
        
        if (cursor != null && columnName != null) {
            int index = cursor.getColumnIndex(columnName);
            if (index >= 0) {
                int integerValue = cursor.getInt(index);
                value = integerValue == INTEGER_TRUE;
            }
        }
        
        return value;
    }
}
